﻿using CustomControls.PoptipCtr;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using Newtonsoft.Json;
using ManagementTool.Config;

namespace ManagementTool.DB.Control
{
    public class MySqlDbControl
    {
        private string SqlConnString
        {
            get;
            set;
        }

        public MySqlDbControl()
        {
            string _File=AppDomain.CurrentDomain.BaseDirectory+ "\\Config\\MyConfigModel.json";
            if (File.Exists(_File))
            {
                string StrCotent = File.ReadAllText(_File);
                MyConfigModel itemModel = JsonConvert.DeserializeObject<MyConfigModel>(StrCotent);
                if (itemModel != null)
                {
                    SqlConnString = $"Server={itemModel.ServerAddress};Port= {itemModel.ServerPort};Database={itemModel.DataBase};Uid={itemModel.UserName};Pwd={itemModel.PassWord};charset='utf8';SslMode=none";
                }
            }
            else 
            {
               
            }
           
        }

        public bool IsDBOpen()
        {
            bool flg = false;
            try
            {
                using (MySqlConnection Conn = new MySqlConnection(SqlConnString))
                {
                    Conn.Open();
                    flg = true;
                    Conn.Close();

                }
            }
            catch (Exception ex)
            {
                
            }
            return flg;
        }

        #region 
        /// <summary>
        /// 工艺参数
        /// </summary>
        /// <param name="_sql"></param>
        /// <returns></returns>
        public Dictionary<string, string> GetProcessParameters(string _sql)
        {
            var itemList = new Dictionary<string, string>();
            try
            {
                using (MySqlConnection Conn = new MySqlConnection(SqlConnString))
                {
                    Conn.Open();
                    MySqlCommand command = new MySqlCommand(_sql, Conn);
                    MySqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            itemList.Add(reader[1].ToString().Trim(), reader[0].ToString().Trim());
                        }
                    }
                    Conn.Close();
                    Conn.Dispose();
                    reader.Close();
                    reader.Dispose();
                    command.Dispose();
                }
            }
            catch (Exception ex)
            {
              
            }
            return itemList;
        }

        public List<string> GetCommonList(string _sql, bool isNeedSplit = false)
        {
            var devList = new List<string>();
            try
            {
                using (MySqlConnection Conn = new MySqlConnection(SqlConnString))
                {
                    Conn.Open();
                    MySqlCommand command = new MySqlCommand(_sql, Conn);
                    MySqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            if (isNeedSplit)
                            {
                                string itemStr = "[" + reader[0].ToString().Trim() + "]" + " " + reader[1].ToString().Trim();
                                devList.Add(itemStr);
                            }
                            else
                            {
                                devList.Add(reader[0].ToString().Trim());
                            }
                        }
                    }
                    Conn.Close();
                    Conn.Dispose();
                    reader.Close();
                    reader.Dispose();
                    command.Dispose();
                }
            }
            catch (Exception ex)
            {
               
            }
            return devList;
        }


        public string GetCommonStringType(string _sql)
        {
            string _stringContent = "";
            try
            {
                using (MySqlConnection Conn = new MySqlConnection(SqlConnString))
                {
                    Conn.Open();
                    MySqlCommand command = new MySqlCommand(_sql, Conn);
                    MySqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            _stringContent = reader["id"].ToString().Trim() + "," + reader["machine_brand"].ToString().Trim() + "," + reader["machine_type"].ToString().Trim() + "," + reader["mach_model_id"].ToString().Trim();
                        }
                    }
                    Conn.Close();
                    Conn.Dispose();
                    reader.Close();
                    reader.Dispose();
                    command.Dispose();
                }
            }
            catch (Exception ex)
            {
               
            }
            return _stringContent;
        }


        public bool GetTargetDBIsHaveDate(string _sql)
        {
            bool isresult = false;
            try
            {
                using (MySqlConnection Conn = new MySqlConnection(SqlConnString))
                {
                    Conn.Open();
                    MySqlCommand command = new MySqlCommand(_sql, Conn);
                    MySqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        isresult = true;
                    }
                    Conn.Close();
                    Conn.Dispose();
                    reader.Close();
                    reader.Dispose();
                    command.Dispose();
                }
            }
            catch (Exception ex)
            {
                
            }
            return isresult;
        }


        public int ExeCuteCommonSql(string _sql)
        {
            int result = -1;
            try
            {
                using (MySqlConnection Conn = new MySqlConnection(SqlConnString))
                {
                    Conn.Open();
                    MySqlCommand command = new MySqlCommand(_sql, Conn);
                    result = command.ExecuteNonQuery();
                    Conn.Close();
                    Conn.Dispose();
                    command.Dispose();
                }
            }
            catch (Exception ex)
            {
               
            }
            return result;
        }


        public List<string> GetConfigDate(string _sql)
        {
            var devList = new List<string>();
            try
            {
                using (MySqlConnection Conn = new MySqlConnection(SqlConnString))
                {
                    Conn.Open();
                    MySqlCommand command = new MySqlCommand(_sql, Conn);
                    MySqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            devList.Add(reader[1].ToString().Trim());
                            devList.Add(reader[2].ToString().Trim());
                            devList.Add(reader[3].ToString());
                            devList.Add(reader[4].ToString().Trim());
                            devList.Add(reader[5].ToString().Trim());
                            devList.Add(reader[6].ToString().Trim());
                            devList.Add(reader[7].ToString().Trim());
                            devList.Add(reader[8].ToString().Trim());
                            devList.Add(reader[9].ToString().Trim());
                            devList.Add(reader[10].ToString().Trim());
                            devList.Add(reader[11].ToString().Trim());
                            devList.Add(reader[12].ToString().Trim());
                            devList.Add(reader[13].ToString().Trim());
                            devList.Add(reader[14].ToString().Trim());
                            break;
                        }
                    }
                    Conn.Close();
                    Conn.Dispose();
                    reader.Close();
                    reader.Dispose();
                    command.Dispose();
                }
            }
            catch (Exception ex)
            {
                
            }
            return devList;
        }


        public int GetCommonDateCount(string _sql)
        {
            int Num = 0;
            DataSet data = new DataSet();
            try
            {
                using (MySqlConnection Conn = new MySqlConnection(SqlConnString))
                {
                    Conn.Open();
                    MySqlDataAdapter command = new MySqlDataAdapter(_sql, Conn);
                    command.Fill(data);
                    if (data.Tables.Count > 0)
                    {
                        Num = Convert.ToInt32(data.Tables[0].Rows[0].ItemArray[0]);
                    }
                    Conn.Close();
                    Conn.Dispose();
                    command.Dispose();
                    data.Dispose();
                    data = null;
                }

            }
            catch (Exception ex)
            {
                
            }
            return Num;
        }



        #region New
        public DataTable ExecuteQuery(string _sql)
        {
            DataTable data = new DataTable();
            try
            {
                using (MySqlConnection Conn = new MySqlConnection(SqlConnString))
                {
                    Conn.Open();

                    MySqlCommand command = new MySqlCommand(_sql, Conn);
                    using (var sdr = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        data.Load(sdr);
                    }
                }

            }
            catch (Exception ex)
            {
                MessageControl.Warning("操作失败!", "DialogRootMessageTooken");
               
            }
            return data;
        }

        public int ExecuteNonQuery(string _sql)
        {
            int result = 0;
            try
            {
                using (MySqlConnection Conn = new MySqlConnection(SqlConnString))
                {
                    Conn.Open();
                    MySqlCommand command = new MySqlCommand(_sql, Conn);
                    result = command.ExecuteNonQuery();
                    Conn.Close();
                    Conn.Dispose();
                    command.Dispose();
                }
            }
            catch (Exception ex)
            {
               
            }
            return result;
        }
        #endregion

        #endregion

    }
}
